package com.chinasofti.myjob.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.chinasofti.myjob.exception.DAOException;

public class DBUtil {
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/myjob";
	private static final String NAME = "root";
	private static final String PASSWORD = "2333";
	private static ThreadLocal<Connection> conns = new ThreadLocal<>();//保证在一次操作中只有唯一的实例
	private static BasicDataSource dataSource = buildDataSource();//数据库连接池
	
	private DBUtil() {
	}
	
	private static BasicDataSource buildDataSource() {
		BasicDataSource ds = new BasicDataSource();
		ds.setDriverClassName(DRIVER);
		ds.setUrl(URL);
		ds.setUsername(NAME);
		ds.setPassword(PASSWORD);
		return ds;
	}
	
	public static DataSource getDataSource() {
		return dataSource;
	}
	
	/**
	 *获取数据库连接
	 */
	public static Connection getConnection() {
		Connection conn = conns.get();
		if(conn == null) {
			try {
				conn = dataSource.getConnection();
				conns.set(conn);
			} catch (SQLException e) {
				throw new DAOException("获取SQL连接失败!",e);
			}
		}
		return conn;
	}
	
	/**
	 *关闭数据库连接
	 */
	public static void closeConnection(Connection conn) {
		try {
			if(conn != null && !conn.isClosed()) {
				conn.close();
			}
			conns.remove();
		} catch (SQLException e) {
			throw new DAOException("SQL关闭失败!",e);
		}
	}
	
	/**
	 * 执行单条sql语句
	 */
	public static void executeSQL(String sql,Object... params) {
		Connection  conn = getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			runner.update(conn, sql,params);
		} catch (SQLException e) {
			throw new DAOException("执行SQL失败!",e);
		}
	}
	
	/**
	 * 批量执行sql语句
	 */
	public static void executeBathcSQL(String sql,Object[]... params) {
		Connection conn = getConnection();
		QueryRunner runner = new QueryRunner();
		try {
			runner.batch(conn, sql, params);
		} catch (SQLException e) {
			throw new DAOException("执行SQL失败!",e);
		}
	}
	
	/**
	 * 执行查询sql,并将结果封装成List<Bean>
	 */
	public static <T> List<T> getResult(String sql,Class<T> cls,Object... params) {
		Connection conn = getConnection();
		QueryRunner runner = new QueryRunner();
		List<T> data = new ArrayList<>();
		try {
			data = runner.query(conn,sql, new BeanListHandler<T>(cls),params);
		} catch (SQLException e) {
			throw new DAOException("数据查询失败!",e);
		}
		return data;
	}
	
	/**
	 * 执行查询sql,并将结果封装成Bean
	 */
	public static <T> T getSingleResult(String sql,Class<T> cls,Object... params) {
		Connection conn = getConnection();
		QueryRunner runner = new QueryRunner();
		T data = null;
		try {
			data = runner.query(conn, sql,new BeanHandler<T>(cls),params);
		} catch (SQLException e) {
			throw new DAOException("数据查询失败!",e);
		}
		return data;
	}
	
	/**
	 * 查询标量数据,如聚合函数的查询结果
	 */
	public static <T> T getScalarResult(String sql,Class<T> cls,Object... params) {
		Connection conn = getConnection();
		QueryRunner runner = new QueryRunner();
		T data = null;
		try {
			data = runner.query(conn, sql,new ScalarHandler<T>(),params);
		} catch (SQLException e) {
			throw new DAOException("数据查询失败!",e);
		}
		return data;
	}
}
